#!/usr/bin/env python3
"""
MySQL数据库管理工具
提供数据库操作的便捷接口
"""

import sys
import os
import configparser
import time
from pathlib import Path
from datetime import datetime

# 添加项目根目录到Python路径
project_root = Path(__file__).parent.parent
sys.path.append(str(project_root))

try:
    import pymysql
    import pandas as pd
except ImportError as e:
    print(f"❌ 导入依赖失败: {e}")
    print("请安装: pip install pymysql pandas")
    sys.exit(1)


class MySQLManager:
    """MySQL数据库管理器"""
    
    def __init__(self, config_file=None):
        """初始化管理器"""
        self.config = self._load_config(config_file)
        self.connection = None
    
    def _load_config(self, config_file):
        """加载配置文件"""
        if config_file is None:
            config_file = Path(__file__).parent / 'mysql_config.ini'
        
        config = configparser.ConfigParser()
        
        if config_file.exists():
            config.read(config_file)
        else:
            # 默认配置
            config['mysql'] = {
                'host': 'localhost',
                'port': '3306',
                'user': 'root',
                'password': '',
                'database': 'mobile_data_sim'
            }
        
        return config
    
    def connect(self):
        """连接数据库"""
        try:
            self.connection = pymysql.connect(
                host=self.config['mysql']['host'],
                port=int(self.config['mysql']['port']),
                user=self.config['mysql']['user'],
                password=self.config['mysql']['password'],
                database=self.config['mysql']['database'],
                charset='utf8mb4',
                autocommit=True
            )
            return True
        except Exception as e:
            print(f"❌ 数据库连接失败: {e}")
            return False
    
    def disconnect(self):
        """断开连接"""
        if self.connection:
            self.connection.close()
            self.connection = None
    
    def execute_query(self, query, params=None):
        """执行查询"""
        if not self.connection:
            if not self.connect():
                return None
        
        try:
            cursor = self.connection.cursor()
            cursor.execute(query, params)
            
            if query.strip().upper().startswith('SELECT'):
                return cursor.fetchall()
            else:
                return cursor.rowcount
                
        except Exception as e:
            print(f"❌ 查询执行失败: {e}")
            return None
    
    def get_table_info(self, table_name='user_records'):
        """获取表信息"""
        info = {}
        
        # 表是否存在
        result = self.execute_query("SHOW TABLES LIKE %s", (table_name,))
        info['exists'] = len(result) > 0 if result and isinstance(result, (list, tuple)) else False
        
        if not info['exists']:
            return info
        
        # 记录数
        result = self.execute_query(f"SELECT COUNT(*) FROM {table_name}")
        info['record_count'] = result[0][0] if result else 0
        
        # 表结构
        result = self.execute_query(f"DESCRIBE {table_name}")
        info['columns'] = len(result) if result else 0
        
        # 表大小
        result = self.execute_query("""
            SELECT 
                ROUND(((data_length + index_length) / 1024 / 1024), 2) AS size_mb
            FROM information_schema.tables 
            WHERE table_schema = DATABASE() AND table_name = %s
        """, (table_name,))
        info['size_mb'] = result[0][0] if result else 0
        
        return info
    
    def get_data_statistics(self, table_name='user_records'):
        """获取数据统计"""
        stats = {}
        
        # 异网用户分布
        result = self.execute_query(f"""
            SELECT 
                LABEL_IS_DIFF,
                COUNT(*) as count,
                ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM {table_name}), 2) as percentage
            FROM {table_name} 
            GROUP BY LABEL_IS_DIFF
        """)
        
        if result:
            stats['user_type_distribution'] = {
                '正常用户' if row[0] == 0 else '异网用户': {'count': row[1], 'percentage': row[2]}
                for row in result
            }
        
        # 性别分布
        result = self.execute_query(f"""
            SELECT 
                ATTR_GENDER,
                COUNT(*) as count
            FROM {table_name} 
            WHERE ATTR_GENDER IS NOT NULL
            GROUP BY ATTR_GENDER
        """)
        
        if result:
            stats['gender_distribution'] = {row[0]: row[1] for row in result}
        
        # 区域分布（前10）
        result = self.execute_query(f"""
            SELECT ATTR_CITY, COUNT(*) as count 
            FROM {table_name} 
            GROUP BY ATTR_CITY 
            ORDER BY count DESC 
            LIMIT 10
        """)
        
        if result:
            stats['city_distribution'] = {row[0]: row[1] for row in result}
        
        # 消费统计
        result = self.execute_query(f"""
            SELECT 
                AVG(METRIC_MONTHLY_CONSUMPTION) as avg_consumption,
                MIN(METRIC_MONTHLY_CONSUMPTION) as min_consumption,
                MAX(METRIC_MONTHLY_CONSUMPTION) as max_consumption,
                STD(METRIC_MONTHLY_CONSUMPTION) as std_consumption
            FROM {table_name} 
            WHERE LABEL_IS_DIFF = 0 AND METRIC_MONTHLY_CONSUMPTION IS NOT NULL
        """)
        
        if result and result[0][0]:
            stats['consumption_stats'] = {
                'avg': round(result[0][0], 2),
                'min': round(result[0][1], 2),
                'max': round(result[0][2], 2),
                'std': round(result[0][3], 2) if result[0][3] else 0
            }
        
        return stats
    
    def export_data(self, query, filename, format='csv'):
        """导出数据"""
        try:
            if not self.connection:
                if not self.connect():
                    return False
            
            df = pd.read_sql(query, self.connection)
            
            if format.lower() == 'csv':
                df.to_csv(filename, index=False, encoding='utf-8-sig')
            elif format.lower() == 'excel':
                df.to_excel(filename, index=False)
            elif format.lower() == 'json':
                df.to_json(filename, orient='records', force_ascii=False, indent=2)
            
            print(f"✅ 数据导出成功: {filename}")
            print(f"   记录数: {len(df):,}")
            print(f"   文件大小: {os.path.getsize(filename) / 1024 / 1024:.2f} MB")
            
            return True
            
        except Exception as e:
            print(f"❌ 数据导出失败: {e}")
            return False
    
    def backup_table(self, table_name='user_records', backup_dir='../backups'):
        """备份表"""
        try:
            backup_dir = Path(backup_dir)
            backup_dir.mkdir(exist_ok=True)
            
            timestamp = datetime.now().strftime('%Y%m%d_%H%M%S')
            backup_file = backup_dir / f"{table_name}_backup_{timestamp}.sql"
            
            # 使用mysqldump备份
            cmd = f"""mysqldump -h {self.config['mysql']['host']} \
                     -P {self.config['mysql']['port']} \
                     -u {self.config['mysql']['user']} \
                     --single-transaction --routines --triggers \
                     {self.config['mysql']['database']} {table_name} > {backup_file}"""
            
            if self.config['mysql']['password']:
                cmd = cmd.replace(f"-u {self.config['mysql']['user']}", 
                                f"-u {self.config['mysql']['user']} -p{self.config['mysql']['password']}")
            
            os.system(cmd)
            
            if backup_file.exists():
                print(f"✅ 表备份成功: {backup_file}")
                print(f"   文件大小: {backup_file.stat().st_size / 1024 / 1024:.2f} MB")
                return str(backup_file)
            else:
                print(f"❌ 表备份失败")
                return None
                
        except Exception as e:
            print(f"❌ 表备份失败: {e}")
            return None
    
    def optimize_table(self, table_name='user_records'):
        """优化表"""
        try:
            print(f"🔧 优化表 {table_name}...")
            
            # 分析表
            result = self.execute_query(f"ANALYZE TABLE {table_name}")
            print(f"✅ 表分析完成")
            
            # 优化表
            result = self.execute_query(f"OPTIMIZE TABLE {table_name}")
            print(f"✅ 表优化完成")
            
            return True
            
        except Exception as e:
            print(f"❌ 表优化失败: {e}")
            return False
    
    def show_status(self):
        """显示数据库状态"""
        print("📊 数据库状态报告")
        print("=" * 50)
        
        # 连接信息
        print(f"🔌 连接信息:")
        print(f"   主机: {self.config['mysql']['host']}:{self.config['mysql']['port']}")
        print(f"   用户: {self.config['mysql']['user']}")
        print(f"   数据库: {self.config['mysql']['database']}")
        
        if not self.connect():
            return
        
        # 数据库版本
        result = self.execute_query("SELECT VERSION()")
        if result:
            print(f"   版本: {result[0][0]}")
        
        # 表信息
        table_info = self.get_table_info()
        print(f"\n📋 主表信息:")
        print(f"   表存在: {'是' if table_info.get('exists') else '否'}")
        if table_info.get('exists'):
            print(f"   记录数: {table_info.get('record_count', 0):,}")
            print(f"   字段数: {table_info.get('columns', 0)}")
            print(f"   表大小: {table_info.get('size_mb', 0):.2f} MB")
        
        # 数据统计
        if table_info.get('exists') and table_info.get('record_count', 0) > 0:
            stats = self.get_data_statistics()
            
            if 'user_type_distribution' in stats:
                print(f"\n👥 用户类型分布:")
                for user_type, data in stats['user_type_distribution'].items():
                    print(f"   {user_type}: {data['count']:,} ({data['percentage']:.1f}%)")
            
            if 'gender_distribution' in stats:
                print(f"\n⚧ 性别分布:")
                for gender, count in stats['gender_distribution'].items():
                    print(f"   {gender}: {count:,}")
            
            if 'consumption_stats' in stats:
                print(f"\n💰 消费统计:")
                cs = stats['consumption_stats']
                print(f"   平均: {cs['avg']:.2f}元")
                print(f"   最小: {cs['min']:.2f}元")
                print(f"   最大: {cs['max']:.2f}元")
                print(f"   标准差: {cs['std']:.2f}元")


def main():
    """主函数"""
    import argparse
    
    parser = argparse.ArgumentParser(description='MySQL数据库管理工具')
    parser.add_argument('--config', help='配置文件路径')
    
    subparsers = parser.add_subparsers(dest='command', help='可用命令')
    
    # status命令
    subparsers.add_parser('status', help='显示数据库状态')
    
    # export命令
    export_parser = subparsers.add_parser('export', help='导出数据')
    export_parser.add_argument('--query', default='SELECT * FROM user_records LIMIT 1000', help='SQL查询')
    export_parser.add_argument('--output', required=True, help='输出文件名')
    export_parser.add_argument('--format', choices=['csv', 'excel', 'json'], default='csv', help='输出格式')
    
    # backup命令
    backup_parser = subparsers.add_parser('backup', help='备份表')
    backup_parser.add_argument('--table', default='user_records', help='表名')
    backup_parser.add_argument('--dir', default='../backups', help='备份目录')
    
    # optimize命令
    optimize_parser = subparsers.add_parser('optimize', help='优化表')
    optimize_parser.add_argument('--table', default='user_records', help='表名')
    
    args = parser.parse_args()
    
    if not args.command:
        parser.print_help()
        return
    
    # 创建管理器
    manager = MySQLManager(args.config)
    
    try:
        if args.command == 'status':
            manager.show_status()
            
        elif args.command == 'export':
            manager.export_data(args.query, args.output, args.format)
            
        elif args.command == 'backup':
            manager.backup_table(args.table, args.dir)
            
        elif args.command == 'optimize':
            manager.optimize_table(args.table)
            
    finally:
        manager.disconnect()


if __name__ == "__main__":
    main()
